<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">

    <changeSet author="sb" id="authservice-users-table">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="users" />
            </not>
        </preConditions>

        <createTable tableName="users">
            <column autoIncrement="true" name="user_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="authserviceuseridprimarykey"/>
            </column>
            <column name="username" type="VARCHAR(64)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="password" type="VARCHAR(64)">
                <constraints nullable="false"/>
            </column>
            <column name="password_salt" type="VARCHAR(64)">
                <constraints nullable="false"/>
            </column>
            <column name="email" type="VARCHAR(64)">
                <constraints nullable="false"/>
            </column>
            <column name="firstname" type="VARCHAR(256)">
                <constraints nullable="false"/>
            </column>
            <column name="lastname" type="VARCHAR(256)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex indexName="ix_users_id" tableName="users">
            <column name="user_id" type="INTEGER" />
        </createIndex>

        <createIndex indexName="ix_users_username" tableName="users">
            <column name="username" type="VARCHAR(64)" />
        </createIndex>

    </changeSet>


    <changeSet author="sb" id="authservice-roles-table">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="roles" />
            </not>
        </preConditions>

        <createTable tableName="roles">
            <column autoIncrement="true" name="role_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="roleidprimarykey"/>
            </column>
            <column name="role_name" type="VARCHAR(64)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="description" type="VARCHAR(128)">
            </column>
        </createTable>

        <createIndex indexName="ix_roles_id" tableName="roles">
            <column name="role_id" type="INTEGER" />
        </createIndex>

        <createIndex indexName="ix_roles_rolename" tableName="roles">
            <column name="role_name" type="VARCHAR(64)" />
        </createIndex>

    </changeSet>


    <changeSet author="sb" id="authservice-userroles-table">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="user_roles" />
            </not>
        </preConditions>

        <createTable tableName="user_roles">
            <column autoIncrement="true" name="user_role_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="userroleidprimarykey"/>
            </column>
            <column name="role_name" type="VARCHAR(64)">
                <constraints nullable="false" foreignKeyName="fk_user_roles_role_name" references="roles(role_name)"/>
            </column>
            <column name="username" type="VARCHAR(64)">
                <constraints nullable="false"  foreignKeyName="fk_user_roles_username" references="users(username)"/>
            </column>
        </createTable>

        <createIndex indexName="ix_user_roles_id" tableName="user_roles">
            <column name="user_role_id" type="INTEGER" />
        </createIndex>

        <createIndex indexName="ix_user_roles_rolename" tableName="user_roles">
            <column name="role_name" type="VARCHAR(64)" />
        </createIndex>

        <createIndex indexName="ix_user_roles_username" tableName="user_roles">
            <column name="username" type="VARCHAR(64)" />
        </createIndex>

    </changeSet>


    <changeSet author="sb" id="authservice-permissions-table">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="permissions" />
            </not>
        </preConditions>

        <createTable tableName="permissions">
            <column autoIncrement="true" name="permission_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="permissionidprimarykey"/>
            </column>
            <column name="permission_name" type="VARCHAR(64)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="description" type="VARCHAR(128)">
            </column>
        </createTable>

        <createIndex indexName="ix_permissions_id" tableName="permissions">
            <column name="permission_id" type="INTEGER" />
        </createIndex>

        <createIndex indexName="ix_permissions_permissionname" tableName="permissions">
            <column name="permission_name" type="VARCHAR(64)" />
        </createIndex>

    </changeSet>


    <changeSet author="sb" id="authservice-rolespermissions-table">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="roles_permissions" />
            </not>
        </preConditions>

        <createTable tableName="roles_permissions">
            <column autoIncrement="true" name="user_role_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="rolepermissionidprimarykey"/>
            </column>
            <column name="role_name" type="VARCHAR(64)">
                <constraints nullable="false" foreignKeyName="fk_roles_permissions_role_name" references="roles(role_name)"/>
            </column>
            <column name="permission_name" type="VARCHAR(64)">
                <constraints nullable="false"  foreignKeyName="fk_roles_permissions_permission_name" references="permissions(permission_name)"/>
            </column>
        </createTable>

        <createIndex indexName="ix_roles_permissions_id" tableName="roles_permissions">
            <column name="user_role_id" type="INTEGER" />
        </createIndex>

        <createIndex indexName="ix_roles_permissions_rolename" tableName="roles_permissions">
            <column name="role_name" type="VARCHAR(64)" />
        </createIndex>

        <createIndex indexName="ix_roles_permissions_username" tableName="roles_permissions">
            <column name="permission_name" type="VARCHAR(64)" />
        </createIndex>

    </changeSet>

    <changeSet author="sb" id="insert-admin-user-if-not-present">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">
                select count(*) from users where username='admin'
            </sqlCheck>
        </preConditions>
        <sql>
            insert into users (username,password,password_salt,email,firstname,lastname) values ('admin','e0/CP6ewTISoWCNfJtUNw/vHTEWl6lXlXGjmE+hBH1o=', 'Iu9wre2JgXuxvRT2MA0CGQ==','admin@localhost','Admin','Istrator');
        </sql>
    </changeSet>

    <changeSet author="sb" id="insert-role-useradmin-if-not-present">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">
                select count(*) from roles where role_name='useradmin'
            </sqlCheck>
        </preConditions>
        <sql>
            insert into roles (role_name, description) values ('useradmin','Administrate users')
        </sql>
    </changeSet>

    <changeSet author="sb" id="add-user-role-useradmin-to-admin-if-not-already-present">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">
                select count(*) from user_roles where role_name='useradmin' and username='admin'
            </sqlCheck>
        </preConditions>
        <sql>
            insert into user_roles (role_name, username) values ('useradmin','admin')
        </sql>
    </changeSet>

</databaseChangeLog>
